package com.ibm.moa.outmail.utils;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ibm.moa.outmail.MailApplication;
import com.ibm.moa.outmail.module.MailInfo;


public class DBHandler extends SQLiteOpenHelper{

	public DBHandler(Context context) {
		super(context, "MailDB", null, 2);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS caogaoTab (timeMillis INTEGER NOT NULL," +
				"userId varchar(20) ,recAddr varchar(100)," +
				"mailCc varchar(100),mailBcc varchar(100),subject varchar(500),plainContent varchar(2000),preContent varchar(2000))");
		db.execSQL("CREATE TABLE IF NOT EXISTS sendTab (timeMillis INTEGER NOT NULL," +
				"userId varchar(20) ,recAddr varchar(100), mailFrom varchar(100)," +
				"mailCc varchar(100),mailBcc varchar(100),subject varchar(500),plainContent varchar(2000),preContent varchar(2000))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS sendTab");
		onCreate(db);
	}
	
	public void addCaoGao(MailInfo mailInfo){
		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = null;
		try{
			db.execSQL("insert into caogaoTab(timeMillis,userId ,recAddr," +
				"mailCc,mailBcc ,subject ,plainContent ,preContent) values(?,?,?,?,?,?,?,?)", 
				new Object[]{System.currentTimeMillis(),MailApplication.userCode  ,mailInfo.recAddr ,
					mailInfo.mailCc,mailInfo.mailBcc,mailInfo.subject ,mailInfo.plainContent,mailInfo.preContent});
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(cursor != null) {
				try {
					cursor.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				
			}
			if(db != null) {
				try {
					db.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public void updateCaoGao(MailInfo mailInfo){
		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = null;
		try{
			long timeMillis = mailInfo.timeMillis;
			mailInfo.timeMillis = System.currentTimeMillis();
			if(timeMillis>0){
				db.execSQL("update caogaoTab set timeMillis=?,userId=?  ,recAddr=?," +
					"mailCc=?,mailBcc=? ,subject=? ,plainContent=? ,preContent=?  where timeMillis=?", 
					new Object[]{mailInfo.timeMillis,MailApplication.userCode ,mailInfo.recAddr,
						mailInfo.mailCc ,mailInfo.mailBcc ,mailInfo.subject ,mailInfo.plainContent,mailInfo.preContent ,timeMillis});
			}else{
				addCaoGao(mailInfo);
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(cursor != null) {
				try {
					cursor.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				
			}
			if(db != null) {
				try {
					db.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	@SuppressLint("SimpleDateFormat")
	public ArrayList<MailInfo> getCaoGaoList(long timeMillis){
		ArrayList<MailInfo> list =new ArrayList<MailInfo>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		try{
			cursor = db.rawQuery("select * from caogaoTab WHERE userid=? and timeMillis < ?  ORDER BY timeMillis DESC LIMIT 10", new String[]{MailApplication.userCode,timeMillis+""});
		}catch (Exception e) {
			e.printStackTrace();
			if(cursor != null) {
				try {
					cursor.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			if(db != null) {
				try {
					db.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			return list;
		}
		if(cursor != null && cursor.getCount()>0){
			MailInfo mailInfo = null;
			while(cursor.moveToNext()){
				mailInfo = new MailInfo();
				mailInfo.timeMillis = cursor.getLong(cursor.getColumnIndex("timeMillis"));
				mailInfo.recAddr = cursor.getString(cursor.getColumnIndex("recAddr"));
				mailInfo.mailCc = cursor.getString(cursor.getColumnIndex("mailCc"));
				mailInfo.mailBcc = cursor.getString(cursor.getColumnIndex("mailBcc"));
				mailInfo.subject = cursor.getString(cursor.getColumnIndex("subject"));
				mailInfo.plainContent = cursor.getString(cursor.getColumnIndex("plainContent"));
				mailInfo.preContent = cursor.getString(cursor.getColumnIndex("preContent"));
				
				try{
					SimpleDateFormat format;
					Date sendDate = new Date(mailInfo.timeMillis);
					Date date = new Date();
					if(date.getMonth() == sendDate.getMonth() && date.getDate() == sendDate.getDate()){
						format = new SimpleDateFormat("HH:mm");
						mailInfo.sendDateFormat = format.format(sendDate);
					}else{
						format = new SimpleDateFormat("MM月dd日");
						mailInfo.sendDateFormat = format.format(sendDate);
					}
					format = new SimpleDateFormat("yyyy-MM-dd HH:mm"); 
					mailInfo.sendDate = format.format(sendDate);
				}catch (Exception e) {
					e.printStackTrace();
				}
				list.add(mailInfo);
				mailInfo = null;
			}
		}
		if(cursor != null) {
			try {
				cursor.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		if(db != null) {
			try {
				db.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		return list;
	}
	
	/**
	 * 获取草稿箱的总记录数
	 * @return 总记录数
	 */
	public int getCaoGaoCount(){
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		int result = 0;
		try{
			cursor = db.rawQuery("select count(*) from sendTab WHERE userid=?", new String[]{MailApplication.userCode});
		}catch (Exception e) {
			if(cursor != null) {
				try {
					cursor.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			if(db != null) {
				try {
					db.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			return 0;
		}

		if(cursor != null && cursor.getCount()>0){
			cursor.moveToFirst();
			result = cursor.getInt(0);
		}
		if(cursor != null) {
			try {
				cursor.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		if(db != null) {
			try {
				db.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		
		return result;
	}
	
	public void deleteCaoGao(ArrayList<Long> timeMillises){
		SQLiteDatabase db = getWritableDatabase();
		try{
			for(int i = 0; i<timeMillises.size(); i++){
				db.execSQL("DELETE FROM caogaoTab WHERE timeMillis =? and userId = ?", new Object[]{timeMillises.get(i),MailApplication.userCode});
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(db != null) {
				try {
					db.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
		}
	}
	
	public void addSendMail(MailInfo mailInfo){
		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = null;
		try{
			db.execSQL("insert into sendTab(timeMillis,userId ,recAddr,mailFrom," +
				"mailCc,mailBcc ,subject ,plainContent ,preContent) values(?,?,?,?,?,?,?,?,?)", 
				new Object[]{System.currentTimeMillis(),MailApplication.userCode  ,mailInfo.recAddr ,mailInfo.from ,
					mailInfo.mailCc,mailInfo.mailBcc,mailInfo.subject ,mailInfo.plainContent,mailInfo.preContent});
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(cursor != null) {
				try {
					cursor.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				
			}
			if(db != null) {
				try {
					db.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	@SuppressLint("SimpleDateFormat")
	public ArrayList<MailInfo> getSendMailList(long timeMillis){
		ArrayList<MailInfo> list =new ArrayList<MailInfo>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		try{
			cursor = db.rawQuery("select * from sendTab WHERE userid=? and timeMillis < ?  ORDER BY timeMillis DESC LIMIT 10", new String[]{MailApplication.userCode,timeMillis+""});
		}catch (Exception e) {
			e.printStackTrace();
			if(cursor != null) {
				try {
					cursor.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			if(db != null) {
				try {
					db.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			return list;
		}
		if(cursor != null && cursor.getCount()>0){
			MailInfo mailInfo = null;
			while(cursor.moveToNext()){
				mailInfo = new MailInfo();
				mailInfo.timeMillis = cursor.getLong(cursor.getColumnIndex("timeMillis"));
				mailInfo.recAddr = cursor.getString(cursor.getColumnIndex("recAddr"));
				mailInfo.from = cursor.getString(cursor.getColumnIndex("mailFrom"));
				mailInfo.mailCc = cursor.getString(cursor.getColumnIndex("mailCc"));
				mailInfo.mailBcc = cursor.getString(cursor.getColumnIndex("mailBcc"));
				mailInfo.subject = cursor.getString(cursor.getColumnIndex("subject"));
				mailInfo.plainContent = cursor.getString(cursor.getColumnIndex("plainContent"));
				mailInfo.preContent = cursor.getString(cursor.getColumnIndex("preContent"));
				
				try{
					SimpleDateFormat format; 
					Date sendDate = new Date(mailInfo.timeMillis);
					Date date = new Date();
					if(date.getMonth() == sendDate.getMonth() && date.getDate() == sendDate.getDate()){
						format = new SimpleDateFormat("HH:mm");
						mailInfo.sendDateFormat = format.format(sendDate);
					}else{
						format = new SimpleDateFormat("MM月dd日");
						mailInfo.sendDateFormat = format.format(sendDate);
					}
					
					format = new SimpleDateFormat("yyyy-MM-dd HH:mm"); 
					mailInfo.sendDate = format.format(sendDate);
				}catch (Exception e) {
					e.printStackTrace();
				}
				list.add(mailInfo);
				mailInfo = null;
			}
		}
		if(cursor != null) {
			try {
				cursor.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		if(db != null) {
			try {
				db.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		return list;
	}
	
	/**
	 * 获取发件箱的总记录数
	 * @return 总记录数
	 */
	public int getSendCount(){
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		int result = 0;
		try{
			cursor = db.rawQuery("select count(*) from sendTab WHERE userid=?", new String[]{MailApplication.userCode});
		}catch (Exception e) {
			if(cursor != null) {
				try {
					cursor.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			if(db != null) {
				try {
					db.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			return 0;
		}

		if(cursor != null && cursor.getCount()>0){
			cursor.moveToFirst();
			result = cursor.getInt(0);
		}
		if(cursor != null) {
			try {
				cursor.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		if(db != null) {
			try {
				db.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		
		return result;
	}
	
	public void deleteSendMail(ArrayList<Long> timeMillises){
		SQLiteDatabase db = getWritableDatabase();
		try{
			for(int i = 0; i<timeMillises.size(); i++){
				db.execSQL("DELETE FROM sendTab WHERE timeMillis =? and userId = ?", new Object[]{timeMillises.get(i),MailApplication.userCode});
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(db != null) {
				try {
					db.close();
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
		}
	}
	
	
	
	
}
